import Database from "@tauri-apps/plugin-sql";
import { findYueJiangByValue } from "./yuejiang";
import { Store, getStore } from "@tauri-apps/plugin-store";
import { BaseDirectory, dataDir } from "@tauri-apps/api/path";
import { getSeasonByzmonth } from "./dateUtils";
import { getJieQiBySj, getNongliBySj } from "./qikeDateOption";

async function getDatabase() {
  return await Database.load("sqlite:lrskdatabase.db");
}

async function selectYudingZhiz(key) {
  const db = await getDatabase();
  const result = await db.select("select * from zhizhi where key=?", [key]);
  if (result.length > 0) {
    return result[0];
  } else {
    return null;
  }
}

async function selectByValue(value) {
  let c = `%${value}%`;
  alert(c);
  const db = await getDatabase();
  const result = await db.select(
    "select * from zhizhi where key like ? or name like ? or content like ?",
    [c, c, c]
  );
  if (result.length > 0) {
    return result[0];
  } else {
    return null;
  }
}

function restoreQikeObjectForkeli(dbData) {
  // 解析JSON字段
  const nianMing = JSON.parse(dbData.nianMing || "[]");
  const tag = JSON.parse(dbData.tag || "[]");

  // 月将处理代码已移除，您可以在此处添加自定义的月将处理逻辑

  // 复原原始对象结构
  return {
    // 嵌套结构的干支日期时间
    ganzhiDatetime: {
      year: dbData.gzYear,
      month: dbData.gzMonth,
      day: dbData.gzDay,
      hour: dbData.gzHour,
    },

    season: getSeasonByzmonth(dbData.gzMonth[1]) || "",
    jieQi: getJieQiBySj(dbData.sj) || "",
    yueJiang: findYueJiangByValue(dbData.yueJiang), // 月将字段保留为空对象，等待您自定义处理
    gongliDatetime: dbData.sj || "",
    nongliDatetime: getNongliBySj(dbData.sj) || "", // 注意字段名匹配

    nianMing: nianMing,
    zhouyeGui: dbData.zhouyeGui || "",
    guishenType: dbData.guishenType || "",
    shehaiType: dbData.shehaiType || "",
    hourType: dbData.hourType || "",
    yuejiangType: dbData.yuejiangType || "",
    huoShi: dbData.gzHour || "",

    // 嵌套的存当结构
    cunDang: {
      fenZu: dbData.fenZu || "",
      guaZhu: dbData.guaZhu || "",
      guaShi: dbData.guaShi || "",
      zhanShi: dbData.zhanShi || "",
      duanYu: dbData.duanYu || "",
      fanKui: dbData.fanKui || "",
      tag: tag,
    },
  };
}

function restoreQikeObjectForCundang(dbData) {
  // 解析JSON字段
  const nianMing = JSON.parse(dbData.nianMing || "[]");
  const tag = JSON.parse(dbData.tag || "[]");

  // 月将处理代码已移除，您可以在此处添加自定义的月将处理逻辑

  // 复原原始对象结构
  return {
    id: dbData.id,
    // 嵌套结构的干支日期时间
    ganzhiDatetime: {
      year: dbData.gzYear,
      month: dbData.gzMonth,
      day: dbData.gzDay,
      hour: dbData.gzHour,
    },

    season: getSeasonByzmonth(dbData.gzMonth[1]) || "",
    jieQi: getJieQiBySj(dbData.sj) || "",
    yueJiang: findYueJiangByValue(dbData.yueJiang), // 月将字段保留为空对象，等待您自定义处理
    gongliDatetime: dbData.sj || "",
    nongliDatetime: getNongliBySj(dbData.sj) || "", // 注意字段名匹配

    nianMing: nianMing,
    zhouyeGui: dbData.zhouyeGui || "",
    guishenType: dbData.guishenType || "",
    shehaiType: dbData.shehaiType || "",
    hourType: dbData.hourType || "",
    yuejiangType: dbData.yuejiangType || "",
    huoShi: dbData.gzHour || "",

    // 嵌套的存当结构
    cunDang: {
      fenZu: dbData.fenZu || "",
      guaZhu: dbData.guaZhu || "",
      guaShi: dbData.guaShi || "",
      zhanShi: dbData.zhanShi || "",
      duanYu: dbData.duanYu || "",
      fanKui: dbData.fanKui || "",
      tag: tag,
    },
  };
}
async function selectGuFaKeLiByKeyword(key) {
  const db = await getDatabase();
  let result = [];
  let results = [];
  if (key == "@") {
    result = await db.select("select * from gongxiangkeli ", []);
    for (let i = 0; i < result.length; i++) {
      results.push(restoreQikeObjectForkeli(result[i]));
    }
    return results;
  }
  let ks = key.split("@");
  for (const ki of ks) {
    const k = `%${ki}%`;
    // 等待当前查询完成后再进行下一次循环
    const r = await db.select(
      "select * from gongxiangkeli where duanYu like ? or guaShi like ? or zhanShi like ? or fanKui like ? ",
      [k, k, k, k]
    );
    for (let i = 0; i < r.length; i++) {
      results.push(restoreQikeObjectForkeli(r[i]));
    }
    // 将查询结果合并到结果数组
    result.push(...r);
  }

  for (let i = 0; i < result.length; i++) {
    results.push(restoreQikeObjectForkeli(result[i]));
  }
  return results;
}

// 相似课例查询
async function selectXSGuFaKeLi(g, s) {
  const db = await getDatabase();
  const result = await db.select(
    "select * from gufakeli where day = ? and rs =  ?",
    [g, s]
  );
  if (result.length > 0) {
    return result;
  } else {
    return [];
  }
}

// 资料数据功能
async function selectAllZiLiao() {
  const db = await getDatabase();
  const result = await db.select("select * from ziLiao ", []);
  if (result.length > 0) {
    return result;
  } else {
    return [];
  }
}

async function selectZiLiaoByTit(tit) {
  const db = await getDatabase();
  const result = await db.select("select * from book where title = ?", [tit]);
  if (result.length > 0) {
    return result[0];
  } else {
    return [];
  }
}

async function selectBooklist(i) {
  let px = [
    " where ispingbi != 1",
    " where ispingbi != 1 order by latetime asc",
    " where ispingbi != 1 order by rdcount asc",
    " where ispingbi != 1 order by percent asc",
    " where isshoucang == 1 ",
    " where ispingbi == 1 ",
  ];
  const db = await getDatabase();
  const result = await db.select(
    "select id,title,percent,ispingbi,isshoucang from book " + px[i],
    []
  );
  return result;
}

async function updateShoucangbook(item) {
  let sql =
    item.isshoucang == 0
      ? "update book set isshoucang = 1 where id = ?"
      : "update book set isshoucang = 0 where id = ?";
  const db = await getDatabase();
  const result = await db.execute(sql, [item.id]);
  return result;
}

async function updateBookpercent(p, id) {
  const db = await getDatabase();
  const result = await db.execute("update book set percent = ? where id = ?", [
    p,
    id,
  ]);
  return result;
}

async function updateBookopentime(d, id) {
  const db = await getDatabase();
  const result = await db.execute("update book set latetime = ? where id = ?", [
    d,
    id,
  ]);
  return result;
}

async function updatePingbibook(item) {
  let sql =
    item.ispingbi == 0
      ? "update book set ispingbi = 1 where id = ?"
      : "update book set ispingbi = 0 where id = ?";
  const db = await getDatabase();
  const result = await db.execute(sql, [item.id]);
  return result;
}

//？排盘处理功能
async function saveLiurenke(lropt) {
  const db = await getDatabase();
  let s1 = "insert into qkcundang (gzYear,gzMonth,gzDay,gzHour,sj";
  let s2 = " ,yueJiang, shehaiType, guishenType, nianMing,hourType,";
  let s3 =
    "zhouyeGui,fenZu,guaZhu,guaShi,zhanShi,duanYu,fanKui,tag,rss) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
  const result = await db.execute(s1 + s2 + s3, [
    lropt.ganzhiDatetime.year,
    lropt.ganzhiDatetime.month,
    lropt.ganzhiDatetime.day,
    lropt.ganzhiDatetime.hour,
    lropt.gongliDatetime,
    lropt.yueJiang.value,
    lropt.shehaiType,
    lropt.guishenType,
    JSON.stringify(lropt.nianMing),
    lropt.hourType,
    lropt.zhouyeGui,
    lropt.cunDang.fenZu,
    lropt.cunDang.guaZhu,
    lropt.cunDang.guaShi,
    lropt.cunDang.zhanShi,
    lropt.cunDang.duanYu,
    lropt.cunDang.fanKui,
    JSON.stringify(lropt.cunDang.tag),
    lropt.rss,
  ]);
  alert(JSON.stringify(result))
  return result;
}
async function updateLiurenkeById(lropt) {
  // 1. 获取数据库连接
  const db = await getDatabase();

  // 2. 构造 UPDATE SQL（字段与插入方法完全对齐，按 ID 筛选）
  // 注意：字段顺序需与插入时一致，确保数据对应正确
  const updateSql = `
    UPDATE qkcundang 
    SET 
      gzYear = ?,
      gzMonth = ?,
      gzDay = ?,
      gzHour = ?,
      sj = ?,
      yueJiang = ?,
      shehaiType = ?,
      guishenType = ?,
      nianMing = ?,
      hourType = ?,
      zhouyeGui = ?,
      fenZu = ?,
      guaZhu = ?,
      guaShi = ?,
      zhanShi = ?,
      duanYu = ?,
      fanKui = ?,
      tag = ?,
      rss = ?
    WHERE id = ?  -- 按主键 ID 定位要更新的记录
  `;

  // 3. 构造参数数组（顺序与 UPDATE 语句的字段顺序一致，最后追加 ID）
  const params = [
    lropt.ganzhiDatetime.year, // gzYear
    lropt.ganzhiDatetime.month, // gzMonth
    lropt.ganzhiDatetime.day, // gzDay
    lropt.ganzhiDatetime.hour, // gzHour
    lropt.gongliDatetime, // sj
    lropt.yueJiang.value, // yueJiang
    lropt.shehaiType, // shehaiType
    lropt.guishenType, // guishenType
    JSON.stringify(lropt.nianMing), // nianMing（对象转 JSON 字符串，同插入）
    lropt.hourType, // hourType
    lropt.zhouyeGui, // zhouyeGui
    lropt.cunDang.fenZu, // fenZu
    lropt.cunDang.guaZhu, // guaZhu
    lropt.cunDang.guaShi, // guaShi
    lropt.cunDang.zhanShi, // zhanShi
    lropt.cunDang.duanYu, // duanYu
    lropt.cunDang.fanKui, // fanKui
    JSON.stringify(lropt.cunDang.tag), // tag（数组/对象转 JSON 字符串，同插入）
    lropt.rss, // rss
    lropt.id, // WHERE 条件的 ID（最后一个参数）
  ];

  // 4. 执行更新操作（参数化查询，避免 SQL 注入）
  const result = await db.execute(updateSql, params);

  // alert(`更新成功，影响行数：${result.rowsAffected || 0}`); // 按需保留

  return result;
}
async function getLiurenkeByLikeKey(keyword) {
  const db = await getDatabase();
  
  // 定义需要进行模糊查询的所有字段（与更新方法字段对应）
  const allFields = [
    "gzYear",
    "gzMonth",
    "gzDay",
    "gzHour",
    "sj",
    "yueJiang",
    "shehaiType",
    "guishenType",
    "nianMing",
    "hourType",
    "zhouyeGui",
    "fenZu",
    "guaZhu",
    "guaShi",
    "zhanShi",
    "duanYu",
    "fanKui",
    "tag",
    "rss",
  ];

  // 构建查询条件
  let conditions = [];
  const params = [];

  if (keyword && keyword.trim()) {
    const trimmedKeyword = keyword.trim();
    // 对每个字段都添加模糊匹配条件，用OR连接
    allFields.forEach((field) => {
      conditions.push(`${field} LIKE ?`);
      params.push(`%${trimmedKeyword}%`); // 前后加%实现全模糊匹配
    });
  }

  // 构建SQL语句
  let sql = "SELECT * FROM qkcundang";
  if (conditions.length > 0) {
    sql += " WHERE " + conditions.join(" OR "); // 任一字段匹配即返回
  }
  let result = await db.select(sql, params);
  // alert(JSON.stringify(result))
   if (result.length > 0) {
     for (let i = 0; i < result.length; i++) {
       result[i] = restoreQikeObjectForCundang(result[i]);
     }
     return result;
   } else {
     return [];
   }
}
async function deleteLiurenkeByid(id) {
  const db = await getDatabase();
  let r = await db.execute('delete from qkcundang where id = ?', [id])
  return r;
}
async function getLiurenke(fz) {
  let result = [];
  const db = await getDatabase();
  let sql = "select * from qkcundang where fenZu =  ?";
  if (fz == '全部') {
    sql = "select * from qkcundang";
  }
  result = await db.select(sql, [fz]);
  if (result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i] = restoreQikeObjectForCundang(result[i]);
    }
    return result;
  } else {
    return [];
  }
}

async function getLiurenkeBysj(k) {
  let result = [];
  const db = await getDatabase();
  result = await db.select("select * from qkcundang where sj =  ?", [k]);
  if (result.length > 0) {
    for (let i = 0; i < result.length; i++) {
      result[i] = restoreQikeObjectForCundang(result[i]);
    }
    return result[0];
  } else {
    return null;
  }
}

async function addQikecord(date) {
  let db = await getDatabase();
  db.execute("insert into qikecord (createDate) values(?)", [date]);
}
async function deleteQikecord(id) {
  let db = await getDatabase();
  db.execute("delete from qikecord where id = ?", [id]);
}

async function selectAllqikecord() {
  let db = await getDatabase();
  let result = [];
  let r = await db.select("select * from qikecord ", []);
  for (let i = 0; i < r.length; i++) {
    let rs = await db.select("select * from qkcundang where sj = ? ", [
      r[i].createDate,
    ]);

    if (rs.length > 0) {
      let obj = {
        guaZhu: rs[0].guaZhu || "",
        sj: r[i].createDate || "",
        zhanShi: rs[0].zhanShi || "",
        duanYu: rs[0].duanYu || "",
        fanKui: rs[0].fanKui || "",
      };
      result.push(obj);
    }
   
  }
  return result;
}
// 搜搜数据库操作------------------------------------------------------
async function saveSscord(ssObj) {
  const db = await getDatabase();
  const result = await db.execute(
    "insert into sscord (type,keyword,page,date) values(?,?,?,?)",
    [ssObj.type, ssObj.keyword, ssObj.page, ssObj.date]
  );
  return result;
}

async function getSscord() {
  const db = await getDatabase();
  const result = await db.select("select * from sscord ", []);
  if (result.length > 0) {
    return result;
  } else {
    return [];
  }
}

async function upDatesscord(id, page) {
  const db = await getDatabase();
  const result = await db.execute("update sscord set page = ? where id = ?", [
    page,
    id,
  ]);
  if (result.length > 0) {
    alert(JSON.stringify(result[0]));
    return result;
  } else {
    return [];
  }
}

async function deleteSscord(id) {
  const db = await getDatabase();
  const result = await db.execute("delete from sscord where id = ?", [id]);
  if (result.length > 0) {
    alert(JSON.stringify(result[0]));
    return result;
  } else {
    return [];
  }
}
//书籍资料数据库操作区域------------------------------------------------------

export {
  getDatabase,
  selectYudingZhiz,
  selectByValue,
  selectGuFaKeLiByKeyword,
  selectAllZiLiao,
  selectZiLiaoByTit,
  selectXSGuFaKeLi,
  saveLiurenke,
  saveSscord,
  getLiurenke,
  getSscord,
  upDatesscord,
  deleteSscord,
  selectBooklist,
  updatePingbibook,
  updateShoucangbook,
  updateBookpercent,
  updateBookopentime,
  addQikecord,
  deleteQikecord,
  selectAllqikecord,
  getLiurenkeBysj,
  updateLiurenkeById,
  deleteLiurenkeByid,
  getLiurenkeByLikeKey
};
